package com.longway.common.controller;

import com.alibaba.fastjson.JSONObject;
import com.jfinal.kit.JsonKit;
import com.jfinal.kit.Kv;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.DbKit;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.SqlPara;
import com.jfinal.plugin.ehcache.CacheKit;
import com.jfinal.plugin.ehcache.IDataLoader;
import com.longway.common.constant.Const;
import com.longway.common.kit.SqlKit;
import com.longway.core.ext.excel.data.TableDataset;
import com.longway.core.ext.excel.util.DbUtil;
import com.longway.core.ext.render.excel.PoiRender;
import com.longway.core.toolbox.Func;
import com.longway.core.toolbox.kit.DateKit;
import com.longway.core.toolbox.support.SqlKeyword;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

public class ExcelController extends BaseController {

	private static String cacheName = Const.FILE_CACHE;
	
	@SuppressWarnings("unchecked")
	public void preExport() {
		String postdata = getPara("postdata");
		String colnames = getPara("colnames");  
		String colmodel = getPara("colmodel");
		String source = getPara("source");  
		String code = getPara("code");  
		String selfquery = getPara("selfquery");  
		Map<String, String> _postdata = JsonKit.parse(postdata, HashMap.class);
		String[] _colname = colnames.replace("[", "").replace("]", "").split(",");
		List<Map<String, String>> _colmodel = JsonKit.parse(colmodel, ArrayList.class);
	    
		String where = _postdata.get("where");
		String sidx = _postdata.get("sidx");
		String sord = _postdata.get("sord");
		String sort = _postdata.get("sort");
		String order = _postdata.get("order");
		String exwhere = _postdata.get("exwhere");
		if (StrKit.notBlank(sidx)) {
			sort =  sidx + " " + sord + (StrKit.notBlank(sort) ? ("," + sort) : "");
		}
		Map<String, String> map = SqlKeyword.getSqlMap(where);	
		
		Kv param = Kv.by("cond", map);
		if(!Func.isAllEmpty(sort, order)){
			param.set("orderBy","order By " +Func.format(sort) + " " + Func.format(order));
		}
		if(!Func.isEmpty(exwhere)){
			param.set("exwhere",Func.decodeUrl(exwhere));
		}
		if(!Func.isEmpty(selfquery)){
			Map<String, String> selfqueryMap = SqlKeyword.getSqlMap(selfquery);	
			for(String key : selfqueryMap.keySet()){
				param.set(key,selfqueryMap.get(key));
			}
		}
		CacheKit.remove(cacheName, "excel_sql_" + code);
		CacheKit.remove(cacheName, "excel_sql_para" + code);
		CacheKit.remove(cacheName, "excel_colname_" + code);
		CacheKit.remove(cacheName, "excel_colmodel_" + code);
		CacheKit.put(cacheName, "excel_sql_" + code, source);
		CacheKit.put(cacheName, "excel_sql_para" + code, param);
		CacheKit.put(cacheName, "excel_colname_" + code, _colname);
		CacheKit.put(cacheName, "excel_colmodel_" + code, _colmodel);
		
		renderJson(ajaxSucess(code));
	}
	
	/**
	 * excel视图方式
	*/
	@SuppressWarnings("rawtypes")
	public void export() {
		String code = getPara("code");
		String sqlSource = CacheKit.get(cacheName, "excel_sql_" + code);
		Kv param = CacheKit.get(cacheName, "excel_sql_para" + code);
		String [] _colname = CacheKit.get(cacheName, "excel_colname_" + code);
		List<Map<String, String>> _colmodel = CacheKit.get(cacheName, "excel_colmodel_" + code);
		
		List<String> entityList =  new ArrayList<String>();
		StringBuilder sb = new StringBuilder();
		int cnt = 0;
		for (Map<String, String> m : _colmodel) {
			if (cnt > 1) {
				if(Func.format(m.get("hidden")).equals("true")){
					cnt++;
					continue;					
				}
				String name = m.get("name");
				entityList.add(_colname[cnt].replaceAll("\"", ""));
				sb.append(name).append(",");
			}
			cnt++;
		}
		
		String menu_name = getInfoByCode(code, "name");
		if("".equals(menu_name)){
			menu_name = "模板";
		}
		SqlPara sqlPara = Db.getSqlPara(sqlSource, param);
		List dataResult = Db.find(sqlPara);
		String[] header = entityList.toArray(new String[entityList.size()]);
		String[] cloumns = sb.toString().split(",");
		render(PoiRender.me(dataResult).fileName(menu_name + DateKit.getAllTime()+".xls")
				.headers(header).sheetName(menu_name).columns(cloumns).cellWidth(5000).headerRow(1).version("2003"));
	}
	
	public void toExcelCustom() {
		String where  = Func.decodeUrl(getPara("whereMap",""));
		String fileName = Func.decodeUrl(getPara("fileName",""));
		Map<String, String> map = SqlKeyword.getSqlMap(where);	
		String source = getPara("source");
		String colnames = Func.decodeUrl(getPara("colnames",""));
		String colmodel = Func.decodeUrl(getPara("colmodel",""));
		String [] _colname = colnames.split(",");
		List<Map<String, String>> _colmodel = JsonKit.parse(colmodel, ArrayList.class);
		
		List<String> entityList =  new ArrayList<String>();
		StringBuilder sb = new StringBuilder();
		int cnt = 0;
		Map<Integer, Integer> colwidths = new HashMap<>();
		for (Map<String, String> m : _colmodel) {
			String name = m.get("name");
			if(StrKit.notBlank(m.get("width"))){
				colwidths.put(cnt, Integer.parseInt(m.get("width")));
			}
			entityList.add(_colname[cnt].replaceAll("\"", ""));
			sb.append(name).append(",");
			cnt++;
		}
		SqlPara sqlPara = Db.getSqlPara(source, map);
		List<TableDataset> dataResult = new ArrayList<>();
		DbUtil  dbutil = null;
		try {
			Connection conn = DbKit.getConfig().getConnection();
			 dbutil = new DbUtil(conn);
			TableDataset tableDataset = dbutil.executeQuery(sqlPara.getSql(), sqlPara.getPara());
			dataResult.add(tableDataset);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(dbutil !=null){
				dbutil.close();
			}
		}
		String[] header = entityList.toArray(new String[entityList.size()]);
		String[] cloumns = sb.toString().split(",");
		render(PoiRender.me(dataResult).fileName(fileName + DateKit.getAllTime()+".xls")
				.headers(header).sheetName(fileName).columns(cloumns).cellWidth(20).headerRow(1).version("2003").colwidths(colwidths));
	}
	
	private String getInfoByCode(String code, String col) {
		List<Record> menu = CacheKit.get("menuCache", "menu_table_all",
				new IDataLoader() {
					@Override
                    public List<Record> load() {
						return Db.find("select code,pcode,name,url,source,path,tips,isOpen from base_menu order by levels asc,num asc");
					}
				});
		for (Record _menu : menu) {
			if (code.equals(Func.format(_menu.get("code")))) {
				String _info = Func.format(_menu.get(col));
				return _info;
			}
		}
		return "";
	}
	
	
	public void toExcel() {
		String code = getPara("code");  
		String source = getPara("source");  
		String postdata = getPara("postdata");
		String colnames = getPara("colnames");  
		String colmodel = getPara("colmodel");
		Map<String, String> _postdata = JsonKit.parse(postdata, HashMap.class);
		String[] _colname = colnames.replace("[", "").replace("]", "").split(",");
		List<Map<String, Object>> _colmodel = JsonKit.parse(colmodel, ArrayList.class);
	    
		String where = _postdata.get("where");
		String sidx = _postdata.get("sidx");
		String sord = _postdata.get("sord");
		String sort = _postdata.get("sort");
		String order = _postdata.get("order");
		String exwhere = _postdata.get("exwhere");
		if (StrKit.notBlank(sidx)) {
			sort =  sidx + " " + sord + (StrKit.notBlank(sort) ? ("," + sort) : "");
		}
		List<JSONObject> params = SqlKeyword.getSqlParams(where);
		Map<String, Map<String, String>> map  = new LinkedHashMap<String, Map<String, String>>();
		String index,expre,value,connector,groupname="";
		//获取有分组标志的sql条件
		Map<String,List<JSONObject>> groupParams = new HashMap<>();
		for(JSONObject param : params){
			    groupname = param.getString("group");
	            if(groupParams.get(groupname) == null){
	            	groupParams.put(groupname, new ArrayList<JSONObject>());
	            }
	            groupParams.get(groupname).add(param);
		}
		for(String gname : groupParams.keySet()){
			List<JSONObject> groupLP = groupParams.get(gname);
			Map<String, String> group  = new LinkedHashMap<String, String>();
			for(int i=0;i< groupLP.size();i++){
				 JSONObject param = groupLP.get(i);
				 index = param.getString("index");
				 if(SqlKit.checkSQLInject(index)){
							throw new  IllegalArgumentException(index+" column is not Illegal.");
				 }
				 value = param.getString("value");
				 expre = param.getString("expre");
				 connector = param.getString("connector");
				 if("or".equals(connector)){
					 group.put(" or "+index+" "+expre, value);
				 }else{
					 group.put(index+" "+expre, value);
				 }
			}
			map.put(gname, group);
		}
		Kv param = Kv.by("cond", map);
		if(!Func.isAllEmpty(sort, order)){
			param.set("orderBy","order By " +sort + " " + order);
		}
		if(!Func.isEmpty(exwhere)){
			Map<String, String> mapexwhere = SqlKeyword.getSqlMap(exwhere);	
			for(String key : mapexwhere.keySet()){
				param.set(key,mapexwhere.get(key));
			}
		}
		List<String> entityList =  new ArrayList<String>();
		StringBuilder sb = new StringBuilder();
		int cnt = 0;
		Map<Integer, Integer> colwidths = new HashMap<>();
		for (Map<String, Object> m : _colmodel) {
			if (cnt > 1) {
				if(Func.format(m.get("hidden")).equals("true")){
					cnt++;
					continue;					
				}
				String name = (String)m.get("name");
				/*Integer width = (int)m.get("width");
				if(width!=null){
					//colwidths.put(cnt, width*10);
				}*/
				entityList.add(_colname[cnt].replaceAll("\"", ""));
				sb.append(name).append(",");
			}
			cnt++;
		}
		String menu_name = getInfoByCode(code, "name");
		if("".equals(menu_name)){
			menu_name = "模板";
		}
		SqlPara sqlPara = Db.getSqlPara(source, param);
		List<TableDataset> dataResult = new ArrayList<>();
		
		DbUtil  dbutil = null;
		try {
			Connection conn = DbKit.getConfig().getConnection();
			 dbutil = new DbUtil(conn);
			TableDataset tableDataset = dbutil.executeQuery(sqlPara.getSql(), sqlPara.getPara());
			dataResult.add(tableDataset);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(dbutil !=null){
				dbutil.close();
			}
			
		}
		String[] header = entityList.toArray(new String[entityList.size()]);
		String[] cloumns = sb.toString().split(",");
		render(PoiRender.me(dataResult).fileName(menu_name + DateKit.getAllTime()+".xls")
				.headers(header).sheetName(menu_name).columns(cloumns).cellWidth(20).headerRow(1).version("2003").colwidths(colwidths));
	}
	
	public void toExcelDefault() {
		String code = getPara("code");  
		String source = getPara("source");  
		String postdata = getPara("postdata");
		String colnames = getPara("colnames");  
		String colmodel = getPara("colmodel");
		Map<String, String> _postdata = JsonKit.parse(postdata, HashMap.class);
		String[] _colname = colnames.replace("[", "").replace("]", "").split(",");
		List<Map<String, Object>> _colmodel = JsonKit.parse(colmodel, ArrayList.class);
	    
		String where = _postdata.get("where");
		Map<String,String> params = SqlKeyword.getSqlMap(where);
		List<String> entityList =  new ArrayList<String>();
		StringBuilder sb = new StringBuilder();
		int cnt = 0;
		Map<Integer, Integer> colwidths = new HashMap<>();
		for (Map<String, Object> m : _colmodel) {
			if (cnt > 1) {
				if(Func.format(m.get("hidden")).equals("true")){
					cnt++;
					continue;					
				}
				String name = (String)m.get("name");
				/*Integer width = (int)m.get("width");
				if(width!=null){
					//colwidths.put(cnt, width*10);
				}*/
				entityList.add(_colname[cnt].replaceAll("\"", ""));
				sb.append(name).append(",");
			}
			cnt++;
		}
		String menu_name = getInfoByCode(code, "name");
		if("".equals(menu_name)){
			menu_name = "模板";
		}
		SqlPara sqlPara = Db.getSqlPara(source, params);
		List<TableDataset> dataResult = new ArrayList<>();
		DbUtil  dbutil = null;
		try {
			Connection conn = DbKit.getConfig().getConnection();
			 dbutil = new DbUtil(conn);
			TableDataset tableDataset = dbutil.executeQuery(sqlPara.getSql(), sqlPara.getPara());
			dataResult.add(tableDataset);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(dbutil !=null){
				dbutil.close();
			}
		}
		String[] header = entityList.toArray(new String[entityList.size()]);
		String[] cloumns = sb.toString().split(",");
		render(PoiRender.me(dataResult).fileName(menu_name + DateKit.getAllTime()+".xls")
				.headers(header).sheetName(menu_name).columns(cloumns).cellWidth(20).headerRow(1).version("2003").colwidths(colwidths));
	}
	
	
	public void toExcelByTemplate() {
		String templateFile = getPara("templateFile");  
		String sql = getPara("sql");  
		String param = getPara("params");  
		String filename = getPara("fileName");  
		String sheetNames = getPara("sheetNames");  
		Map<String, String> map = SqlKeyword.getSqlMap(param);	
		SqlPara sqlPara = Db.getSqlPara(sql, map);
		List<TableDataset> dataResult = new ArrayList<>();
		DbUtil  dbutil = null;
		try {
			Connection conn = DbKit.getConfig().getConnection();
			dbutil = new DbUtil(conn);
			TableDataset tableDataset = dbutil.executeQuery(sqlPara.getSql(), sqlPara.getPara());
			dataResult.add(tableDataset);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(dbutil !=null){
				dbutil.close();
			}
		}
		render(PoiRender.me(dataResult).outtype(PoiRender.export.TEMPLATE).templateFile(templateFile).fileName(filename+".xls").sheetName(sheetNames).headerRow(2).version("2003"));
	}
}
